﻿using BCCommon;
using BCEntity.Common.Dictionary;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;

namespace BCData.Common.Dictionary
{
    public class DictionaryReferenceData : IDictionaryReferenceData
    {
        #region 私有变量

        private readonly IDatabaseContext databaseContext;

        #endregion 私有变量

        #region 构造函数

        public DictionaryReferenceData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        #endregion 构造函数

        #region 公共方法

        #region 基础检查

        public async Task<bool> CheckName(string name)
        {
            var sql = "SELECT 1 FROM  dictionaryreference WHERE Name=?Name";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Name", name);

            return await databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<bool> CheckRemark(string remark)
        {
            var sql = "SELECT 1 FROM  dictionaryreference WHERE Remark=?Remark";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Remark", remark);

            return await databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<bool> CheckRemarkAndSearchKey(string remark, string searchKey)
        {
            var sql = "SELECT 1 FROM  dictionaryreference WHERE Remark=?Remark AND SearchKey=?SearchKey";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Remark", remark);
            parameters.Add("?SearchKey", searchKey);
            return await databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<bool> CheckRemarkAndName(string remark, string name)
        {
            var sql = "SELECT 1 FROM  dictionaryreference WHERE Remark=?Remark AND Name=?Name";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Remark", remark);
            parameters.Add("?Name", name);
            return await databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<bool> CheckSearchKey(string searchKey)
        {
            var sql = "SELECT 1 FROM  dictionaryreference WHERE searchKey=?searchKey";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?searchKey", searchKey);
            return await databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<int> DeleteAsync(int id)
        {
            string sql = "DELETE FROM dictionaryreference WHERE DictionaryReferenceId= ?DictionaryReferenceId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DictionaryReferenceId", id, DbType.Int32);

            var res = await databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction);
            return res;
        }

        #endregion 基础检查

        #region 增改

        public async Task<int> InsertAsync(DictionaryReferenceEntity entity)
        {
            string sql = @"INSERT INTO dictionaryreference (SearchKey,`Name`,TypeName,ParentName,ItemValue,TypeValue,GroupValue, Remark, AllowEdit,Editer)
                            VALUES(?SearchKey,?Name,?TypeName,?ParentName,?ItemValue,?TypeValue,?GroupValue,?Remark,?AllowEdit,?Editer)";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SearchKey", entity.SearchKey, DbType.AnsiString, size: 150);
            parameters.Add("?Name", entity.Name, DbType.AnsiString, size: 50);
            parameters.Add("?TypeName", entity.TypeName, DbType.AnsiString, size: 50);
            parameters.Add("?ParentName", entity.ParentName, DbType.AnsiString, size: 50);
            parameters.Add("?ItemValue", entity.ItemValue, DbType.AnsiString, size: 500);
            parameters.Add("?TypeValue", entity.TypeValue, DbType.AnsiString, size: 500);
            parameters.Add("?GroupValue", entity.GroupValue, DbType.AnsiString, size: 500);
            parameters.Add("?Remark", entity.Remark, DbType.AnsiString, size: 200);
            parameters.Add("?AllowEdit", entity.AllowEdit, DbType.Boolean);
            parameters.Add("?Editer", entity.Editer, DbType.AnsiString, size: 20);

            var res = await databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction);
            return res;
        }

        public async Task<int> UpdateAsync(DictionaryReferenceEntity entity)
        {
            string sql = @"UPDATE DictionaryReference SET
                                SearchKey = ?SearchKey,
                                NAME = ?NAME,
                                TypeName = ?TypeName,
                                ParentName = ?ParentName,
                                ItemValue = ?ItemValue,
                                TypeValue = ?TypeValue,
                                GroupValue = ?GroupValue,
                                Remark = ?Remark,
                                AllowEdit = ?AllowEdit,
                                Editer = ?Editer
                            WHERE DictionaryReferenceId = ?DictionaryReferenceId;";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SearchKey", entity.SearchKey, DbType.AnsiString, size: 150);
            parameters.Add("?Name", entity.Name, DbType.AnsiString, size: 50);
            parameters.Add("?TypeName", entity.TypeName, DbType.AnsiString, size: 50);
            parameters.Add("?ParentName", entity.ParentName, DbType.AnsiString, size: 50);
            parameters.Add("?ItemValue", entity.ItemValue, DbType.AnsiString, size: 500);
            parameters.Add("?TypeValue", entity.TypeValue, DbType.AnsiString, size: 500);
            parameters.Add("?GroupValue", entity.GroupValue, DbType.AnsiString, size: 500);
            parameters.Add("?Remark", entity.Remark, DbType.AnsiString, size: 200);
            parameters.Add("?AllowEdit", entity.AllowEdit, DbType.Boolean);
            parameters.Add("?Editer", entity.Editer, DbType.AnsiString, size: 20);
            parameters.Add("?DictionaryReferenceId", entity.DictionaryReferenceId, DbType.Int32);

            var res = await databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction);
            return res;
        }

        #endregion 增改

        public async Task<DictionaryReferenceEntity> GetByIdAsync(int dictionaryReferenceId)
        {
            string sql = SqlCommands.GetQuerySql("dictionaryreference", new[]{"DictionaryReferenceId","SearchKey","`Name`","TypeName","ParentName","ItemValue",
                "TypeValue","GroupValue","Remark","AllowEdit","CreateTime","UpdateTime"}, new[] { "DictionaryReferenceId" }, "");

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DictionaryReferenceId", dictionaryReferenceId, DbType.Int32);

            return await databaseContext.Slave.QueryFirstOrDefaultAsync<DictionaryReferenceEntity>(sql, parameters);
        }
        public async Task<DictionaryReferenceEntity> GetEntityAsync(string searchKey)
        {
            string sql = SqlCommands.GetQuerySql("dictionaryreference", new[]{"DictionaryReferenceId","SearchKey","`Name`","TypeName","ParentName","ItemValue",
                "TypeValue","GroupValue","Remark","AllowEdit","CreateTime","UpdateTime"}, new[] { "SearchKey" }, "");
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SearchKey", searchKey, DbType.AnsiString, size: 150);
            return await databaseContext.Slave.QueryFirstOrDefaultAsync<DictionaryReferenceEntity>(sql, parameters);

        }

        public async Task<DictionaryReferenceEntity> GetEntityAsync(string typeName, string name)
        {
            string sql = SqlCommands.GetQuerySql("dictionaryreference", new[]{"DictionaryReferenceId","SearchKey","`Name`","TypeName","ParentName","ItemValue",
                "TypeValue","GroupValue","Remark","AllowEdit","CreateTime","UpdateTime"}, new[] { "TypeName", "Name" }, "");
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TypeName", typeName, DbType.AnsiString, size: 50);
            parameters.Add("?Name", name, DbType.AnsiString, size: 50);
            return await databaseContext.Slave.QueryFirstOrDefaultAsync<DictionaryReferenceEntity>(sql, parameters);
        }

        public async Task<IEnumerable<DictionaryReferenceEntity>> GetByTypeNameAsync(string typeName)
        {
            string sql = SqlCommands.GetQuerySql("dictionaryreference", new[]{"DictionaryReferenceId","SearchKey","`Name`","TypeName","ParentName","ItemValue",
                "TypeValue","GroupValue","Remark","AllowEdit","CreateTime","UpdateTime"}, new[] { "TypeName" }, "");
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TypeName", typeName);

            return await databaseContext.Slave.QueryAsync<DictionaryReferenceEntity>(sql, parameters);
        }

        public async Task<IEnumerable<DictionaryReferenceEntity>> GetByParentNameAsync(string parentName)
        {
            string sql = SqlCommands.GetQuerySql("dictionaryreference", new[]{"DictionaryReferenceId","SearchKey","`Name`","TypeName","ParentName","ItemValue",
                "TypeValue","GroupValue","Remark","AllowEdit","CreateTime","UpdateTime"}, new[] { "ParentName" }, "");
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ParentName", parentName);
            return await databaseContext.Slave.QueryAsync<DictionaryReferenceEntity>(sql, parameters);
        }

        public async Task<IEnumerable<DictionaryReferenceEntity>> GetByTypeNameAndParentNameAsync(string typeName, string parentName)
        {
            string sql = SqlCommands.GetQuerySql("dictionaryreference", new[]{"DictionaryReferenceId","SearchKey","`Name`","TypeName","ParentName","ItemValue",
                "TypeValue","GroupValue","Remark","AllowEdit","CreateTime","UpdateTime"}, new[] { "TypeName", "ParentName" }, "");
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TypeName", typeName);
            parameters.Add("?ParentName", parentName);
            return await databaseContext.Slave.QueryAsync<DictionaryReferenceEntity>(sql, parameters);
        }
        public async Task<IEnumerable<DictionaryReferenceEntity>> GetByInSearchKeyAsync(string searchKeys)
        {
            string sql = SqlCommands.GetQuerySql("dictionaryreference", new[]{"DictionaryReferenceId","SearchKey","`Name`","TypeName","ParentName","ItemValue",
                "TypeValue","GroupValue","Remark","AllowEdit","CreateTime","UpdateTime"}, null, "");
            var wheres = new List<string>();

            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(searchKeys))
            {
                wheres.Add("FIND_IN_SET(SearchKey,?SearchKey)");

                parameters.Add("?SearchKey", searchKeys, DbType.AnsiString);
            }
            sql = $"{sql} {(wheres.Count > 0 ? "WHERE " + String.Join("AND", wheres) : "")}";
            return await databaseContext.Slave.QueryAsync<DictionaryReferenceEntity>(sql, parameters);
        }

        public async Task<IEnumerable<DictionaryReferenceEntity>> GetAsync(string searchKey, string parentName)
        {
            string sql = SqlCommands.GetQuerySql("dictionaryreference", new[]{"DictionaryReferenceId","SearchKey","`Name`","TypeName","ParentName","ItemValue",
                "TypeValue","GroupValue","Remark","AllowEdit","CreateTime","UpdateTime"}, null, "");

            var wheres = new List<string>();

            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(searchKey))
            {
                wheres.Add("SearchKey=?SearchKey");

                parameters.Add("?SearchKey", searchKey, DbType.AnsiString, size: 150);
            }

            if (!string.IsNullOrWhiteSpace(parentName))
            {
                wheres.Add("ParentName=?ParentName");
                parameters.Add("?ParentName", parentName, DbType.AnsiString, size: 50);
            }

            sql = $"{sql} {(wheres.Count > 0 ? "WHERE " + String.Join("AND", wheres) : "")}";

            return await databaseContext.Slave.QueryAsync<DictionaryReferenceEntity>(sql, parameters);
        }

        public async Task<(int dataCount, IEnumerable<DictionaryReferenceEntity>)> GetAsync(int pageIndex, int pageSize, bool ascending, string itemValue, string typeValue)
        {
            string sql = @"SELECT DictionaryReferenceId,SearchKey,`Name`,TypeName,ParentName,ItemValue,TypeValue,GroupValue,Remark,AllowEdit,CreateTime,UpdateTime
                            FROM DictionaryReference";
            DynamicParameters parameters = new DynamicParameters();
            var wheres = new List<string>();
            if (!string.IsNullOrWhiteSpace(itemValue))
            {
                wheres.Add("ItemValue=?itemValue");
                parameters.Add("?itemValue", itemValue, DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(typeValue))
            {
                wheres.Add("TypeValue=?typeValue");
                parameters.Add("?typeValue", typeValue, DbType.AnsiString);
            }
            parameters.Add("?pageIndex", pageIndex, DbType.Int32);
            parameters.Add("?pageSize", pageSize, DbType.Int32);

            sql = string.Format(@"{0} {2} ORDER BY DictionaryReferenceId {1} LIMIT ?pageIndex,?pageSize; SELECT COUNT(1) FROM DictionaryReference {2}", sql,
                ascending ? "ASC" : "DESC", wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "");

            var result = await databaseContext.Slave.QueryMultipleAsync<DictionaryReferenceEntity, int>(sql, parameters);

            var dataList = result.Item1;
            var dataCount = result.Item2;

            return (dataCount, dataList);
        }

        public async Task<IEnumerable<DictionaryReferenceEntity>> GetTypeRecordAsync(string typeName, string parentName = null)
        {
            string sql = SqlCommands.GetQuerySql("dictionaryreference", new[]{"DictionaryReferenceId","SearchKey","`Name`","TypeName","ParentName","ItemValue",
                "TypeValue","GroupValue","Remark","AllowEdit","CreateTime","UpdateTime"}, null, "");

            var wheres = new List<string>();

            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(typeName))
            {
                wheres.Add("TypeName=?TypeName");

                parameters.Add("?TypeName", typeName, DbType.AnsiString, size: 50);
            }

            if (!string.IsNullOrWhiteSpace(parentName))
            {
                wheres.Add("ParentName=?ParentName");
                parameters.Add("?ParentName", parentName, DbType.AnsiString, size: 50);
            }

            sql = $"{sql} {(wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : "")}";

            return await databaseContext.Slave.QueryAsync<DictionaryReferenceEntity>(sql, parameters);
        }


        #endregion 公共方法
    }
}
